"""
Case Type   : View_Iud
Case Name   : 触发器、instead规则和视图插入多条数据的性能
Create At   : 2024/07/25
Owner       : lvlintao666
Description :
        1.创建表,插入一条数据
        2.创建触发器
        3.创建instead规则
        4.查询不同插入数据方式性能
        5.清理环境;删除表
Expect      :
        1.创建表成功
        2.插入数据成功
        3.创建成功
        4.查询成功
        4.删除成功
History     :
"""
import os
import unittest

from testcase.utils.CommonSH import CommonSH
from testcase.utils.Constant import Constant
from testcase.utils.Logger import Logger

class UuidNotRepeat(unittest.TestCase):

    def setUp(self):
        self.log = Logger()
        self.log.info(f'----{os.path.basename(__file__)} start----')
        self.constant = Constant()
        self.comSh = CommonSH('PrimaryDbPGmode')
        self.const = Constant()
        self.table_name = 't_view_0009'
        self.view_name = 'v_view_0009'
        self.f_name = 'f_view_0009'
        self.trigger_name = 'update_trigger'
        self.rule_name = 'rule_001'

    def test_uuid_not_repeat(self):
        text = '------step1:创建表;expect:成功------'
        self.log.info(text)
        create_cmd = f"drop table if exists {self.table_name};" \
                     f"create table {self.table_name}(p_id int);" \
                     f"drop view if exists {self.view_name};" \
                     f"create view {self.view_name} as select * from {self.table_name};" \
                     f"insert into {self.view_name} values(1);"
        self.log.info(create_cmd)
        res = self.comSh.execut_db_sql(create_cmd)
        self.log.info(res)
        self.assertIn(self.const.CREATE_TABLE_SUCCESS, res, '执行失败' + text)
        self.assertIn(self.const.CREATE_VIEW_SUCCESS_MSG, res, '执行失败' + text)
        self.assertIn(self.const.INSERT_SUCCESS_MSG, res, '执行失败' + text)

        text = '------step2:创建触发器;expect:成功------'
        self.log.info(text)
        create_f_cmd = f"create or replace function {self.f_name}() return trigger as " \
                       f"declare " \
                       f"begin " \
                       f"insert into {self.table_name} " \
                       f"values(generate_series(1,400000));" \
                       f"return new;" \
                       f"end;"
        self.log.info(create_f_cmd)
        create_f_result = self.comSh.execut_db_sql(create_f_cmd)
        self.log.info(create_f_result)
        self.assertIn(self.const.CREATE_FUNCTION_SUCCESS_MSG, create_f_result,
                         '执行失败' + text)

        text = '------step3:创建instead规则;expect:成功------'
        tri_rule_cmd = f"create trigger {self.trigger_name} " \
                       f"instead of update on {self.view_name} " \
                       f"for each row execute procedure {self.f_name}();" \
                       f"create rule {self.rule_name} as on insert to " \
                       f"{self.view_name} do instead insert into " \
                       f"{self.table_name} values (generate_series(1,400000));"
        self.log.info(tri_rule_cmd)
        tri_rule_result = self.comSh.execut_db_sql(tri_rule_cmd)
        self.log.info(tri_rule_result)
        self.assertIn(self.const.TRIGGER_CREATE_SUCCESS_MSG, tri_rule_result,
                         '执行失败' + text)
        self.assertIn(self.const.CREATE_RULE_SUCCESS, tri_rule_result,
                         '执行失败' + text)

        text = '------step4:查询触发器插入数据和instead规则插入数据性能对比;expect:成功------'
        self.log.info(text)
        update_sql = f"explain analyze update {self.view_name} set p_id = -1 where p_id = 1;"
        self.log.info(update_sql)
        exec_update_result = self.comSh.execut_db_sql(update_sql)
        self.log.info(exec_update_result)
        trigger_time = exec_update_result.splitlines()[-2].split('|')
        self.log.info(trigger_time)
        exec_trigger_time = trigger_time[0].split(':')[-1].split()[0].strip()
        self.log.info(exec_trigger_time)

        rule_sql = f"explain analyze insert into {self.view_name} values(0);"
        self.log.info(rule_sql)
        exec_rule_result = self.comSh.execut_db_sql(rule_sql)
        self.log.info(exec_rule_result)
        rule_time = exec_rule_result.splitlines()[-2].split('|')
        self.log.info(rule_time)
        exec_rule_time = rule_time[0].split(':')[-1].split()[0].strip()
        self.log.info(exec_rule_time)
        self.assertLess(abs(float(exec_rule_time) - float(exec_trigger_time)), 500, '执行失败' + text)

    def tearDown(self):
        text = '------step5:清理环境;expect:成功------'
        self.log.info(text)

        drop_tri_cmd = f"drop trigger {self.trigger_name} on {self.view_name} cascade;"
        self.log.info(drop_tri_cmd)
        drop_tri_msg = self.comSh.execut_db_sql(drop_tri_cmd)
        self.log.info(drop_tri_msg)

        drop_f_cmd = f"drop function {self.f_name}() cascade;"
        self.log.info(drop_f_cmd)
        drop_f_msg = self.comSh.execut_db_sql(drop_f_cmd)
        self.log.info(drop_f_msg)

        drop_rule_cmd = f"drop rule {self.rule_name} on {self.view_name} cascade;"
        self.log.info(drop_rule_cmd)
        drop_rule_msg = self.comSh.execut_db_sql(drop_rule_cmd)
        self.log.info(drop_rule_msg)

        drop_v_cmd = f"drop view {self.view_name}"
        self.log.info(drop_v_cmd)
        drop_v_msg = self.comSh.execut_db_sql(drop_v_cmd)
        self.log.info(drop_v_msg)

        drop_t_cmd = f"drop table {self.table_name};"
        self.log.info(drop_t_cmd)
        drop_t_msg = self.comSh.execut_db_sql(drop_t_cmd)
        self.log.info(drop_t_msg)

        self.assertIn(self.constant.TRIGGER_DROP_SUCCESS_MSG,
                         drop_tri_msg, '执行失败' + text)
        self.assertIn(self.constant.DROP_FUNCTION_SUCCESS_MSG,
                         drop_f_msg, '执行失败' + text)
        self.assertIn(self.constant.DROP_RULE_SUCCESS,
                         drop_rule_msg, '执行失败' + text)
        self.assertIn(self.constant.DROP_VIEW_SUCCESS_MSG,
                         drop_v_msg, '执行失败' + text)
        self.assertIn(self.constant.DROP_TABLE_SUCCESS,
                         drop_t_msg, '执行失败' + text)
        self.log.info(f'----{os.path.basename(__file__)} end----')
